package com.czlis.boe;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.hutool.core.io.FileUtil;

public class ReadTargetProdspecBox {

	public static String TARGET_PROD = "I10CW22409006,I10CW22408036,I10CW22409007,I10CW22408037,I10CW22408009,I10CW22409004,I10CW22408038,I10CW22409008,I10CW22409009,I10CW22407032,I10CW22409002,I10CW22409003,I10CW22409004,I10CW22409005,I10CW22409003,I10CW22408037,I10CW22408003,I10CW22409002,I10CW22408028,I10CW22408038,I10CW22409004,I10CW22408030,I10CW22409008,I10CW22409036,I10CW22409009,I10CW22409037,I10CW22407030,I10CW22408016,I10CW22407031,I10CW22408019,I10CW22408007,I10CW22409003,I10CW22408004,I10CW22408037,I10CW22407009,I10CW22409035,I10CW22408005,I10CW22407011,I10CW22408006,I10CW22409005,I10CW22409034,I10CW22409007,I10CW22409035,I10CW22409039,I10CW22409036,I10CW22409037,I10CW22409034,I10CW22409038,I10CW22409039,I10CW22409036,I10CW22409040,I10CW22409037,I10CW22409041,I10CW22409038,I10CW22409035,I10CW22409039,I10CW22409040,I10CW22409037,I10CW22409041,I10CW22409038,I10CW22409039,I10CW22409007,I10CW22409039,I10CW22409040,I10CW22409041,I10CW22409038,I10CW22409039,I10CW22409040,I10CW22409041,I10CW22409038,I10CW22409039,I10CW22409036,I10CW22409040,I10CW22409037,I10CW22409041,I13CW22408018,I13CW22408019,I13CW22408020,I13CW22408021,I13CW22408022,I13CW22408023";

	public static String url = "jdbc:oracle:thin:@118.178.86.19:1521/orcl";
	public static String username = "czlis";
	public static String password = "junaiTAO1!";
	public static Connection connection = null;
	public static List<String> ALL_INFOS = new ArrayList<>();
	public static Integer NODATA_COUNT = 0;
	
	
	public static void main(String[] args) throws Exception {
		connection = DriverManager.getConnection(url, username, password);
		int index = 1;
		for (String targetProds : TARGET_PROD.split(",")) {
			StringBuffer sb = new StringBuffer("");
			System.out.print("Read targetproductrequest：" + targetProds + " 序号：" + (index++) + "  ");
			try (
					PreparedStatement statement = connection
							.prepareStatement("select b.boxname from ct_slimifshipbox b where b.targetproductrequest=?")) {
				statement.setString(1, targetProds);
				ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					String oper = resultSet.getString("boxname");
					System.out.print(oper +",");
					sb.append(oper+",");
				}
			}
			System.out.println();
//			System.out.println(sb.toString());
			readAndWriteLog(sb.toString(), targetProds);
			break;
		}
		if(ALL_INFOS.size() > 0) {
			FileUtil.writeLines(ALL_INFOS, new File("D:\\AijunTao\\Desktop\\深圳数据2\\nodata.txt"), "UTF-8");
		}
		
	}

	private static void readAndWriteLog(String boxNames, String targetProd) {
		for(String boxName : boxNames.split(",")) {
			String info = "NoLog boxname：" + boxName + " 委外工单：" + targetProd;
			try (	PreparedStatement statement = connection.prepareStatement(
							"select oper, l.oper_data_all from sys_log l where l.oper like '上传天马深圳%' and l.info like '%成功箱号%"+boxName+"%' and l.operdate>='20240401' order by id asc");
					ResultSet resultSet = statement.executeQuery()) {
				Boolean hasData = false;
				while (resultSet.next()) {
					hasData = true;
					String oper = resultSet.getString("oper");
//					System.out.println(oper);
					java.sql.Clob clob = resultSet.getClob("oper_data_all");
					if (clob != null) {
						String clobContent = clob.getSubString(1, (int) clob.length());
//						System.out.println("CLOB content: " + clobContent);
						String flag = "";
						if(oper.contains("黑名单")) {
							flag = "_黑名单";
						}
						FileUtil.writeString(clobContent, new File("D:\\AijunTao\\Desktop\\深圳数据2\\" + targetProd + "_" +boxName + flag + ".json"), "UTF-8");
					}
				}
				if(!hasData) {
					NODATA_COUNT++;
					System.out.println(NODATA_COUNT + " " + info);
					ALL_INFOS.add(NODATA_COUNT + " " + info);
				}
	
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

}
